package com.iplant.aps.serviceimpl.dao.ipt;

import com.iplant.aps.service.mesenum.APSOutSourceType;
import com.iplant.aps.service.mesenum.IPTOutsourcingMaterialStatus;
import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.excel.MyExcelSheet;
import com.iplant.aps.service.po.ipt.IPTOutsourcingMaterial;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;

public class IPTOutsourcingMaterialDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(IPTOutsourcingMaterialDAO.class);

    private static IPTOutsourcingMaterialDAO Instance = null;

    private IPTOutsourcingMaterialDAO() {
        super();
    }

    public static IPTOutsourcingMaterialDAO getInstance() {
        if (Instance == null)
            Instance = new IPTOutsourcingMaterialDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, IPTOutsourcingMaterial wIPTOutsourcingMaterial,
                      OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wIPTOutsourcingMaterial == null)
                return 0;

            String wSQL = "";
            if (wIPTOutsourcingMaterial.getID() <= 0) {
                wSQL = MessageFormat.format(
                        "INSERT INTO {0}.ipt_outsourcingmaterial(ProductID,LineID,CustomerID,OrderID,PartNo,PartID," +
                                "StepID,TaskStepID,MaterialID,OutSourceType,FQTY,CreateID,CreateTime,SerialNo," +
                                "SuplierNo,SuplierName,Status,IPTItemID,DemandID) VALUES(:ProductID,:LineID," +
								":CustomerID," +
                                ":OrderID,:PartNo,:PartID,:StepID,:TaskStepID,:MaterialID,:OutSourceType,:FQTY," +
                                ":CreateID,:CreateTime,:SerialNo,:SuplierNo,:SuplierName,:Status,:IPTItemID," +
								":DemandID);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format(
                        "UPDATE {0}.ipt_outsourcingmaterial SET ProductID = :ProductID,LineID = :LineID,CustomerID = " +
                                ":CustomerID,OrderID = :OrderID,PartNo = :PartNo,PartID = :PartID,StepID = :StepID," +
                                "TaskStepID = :TaskStepID,MaterialID = :MaterialID,OutSourceType = :OutSourceType," +
                                "FQTY = :FQTY,CreateID = :CreateID,CreateTime = :CreateTime,SerialNo = :SerialNo," +
                                "SuplierNo = :SuplierNo,SuplierName = :SuplierName,Status = :Status," +
                                "IPTItemID=:IPTItemID,DemandID=:DemandID WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("ID", wIPTOutsourcingMaterial.ID);
            wParamMap.put("ProductID", wIPTOutsourcingMaterial.ProductID);
            wParamMap.put("LineID", wIPTOutsourcingMaterial.LineID);
            wParamMap.put("CustomerID", wIPTOutsourcingMaterial.CustomerID);
            wParamMap.put("OrderID", wIPTOutsourcingMaterial.OrderID);
            wParamMap.put("PartNo", wIPTOutsourcingMaterial.PartNo);
            wParamMap.put("PartID", wIPTOutsourcingMaterial.PartID);
            wParamMap.put("StepID", wIPTOutsourcingMaterial.StepID);
            wParamMap.put("TaskStepID", wIPTOutsourcingMaterial.TaskStepID);
            wParamMap.put("MaterialID", wIPTOutsourcingMaterial.MaterialID);
            wParamMap.put("OutSourceType", wIPTOutsourcingMaterial.OutSourceType);
            wParamMap.put("FQTY", wIPTOutsourcingMaterial.FQTY);
            wParamMap.put("CreateID", wIPTOutsourcingMaterial.CreateID);
            wParamMap.put("CreateTime", wIPTOutsourcingMaterial.CreateTime);
            wParamMap.put("SerialNo", wIPTOutsourcingMaterial.SerialNo);
            wParamMap.put("SuplierNo", wIPTOutsourcingMaterial.SuplierNo);
            wParamMap.put("SuplierName", wIPTOutsourcingMaterial.SuplierName);
            wParamMap.put("Status", wIPTOutsourcingMaterial.Status);
            wParamMap.put("IPTItemID", wIPTOutsourcingMaterial.IPTItemID);
            wParamMap.put("DemandID", wIPTOutsourcingMaterial.DemandID);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wIPTOutsourcingMaterial.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wIPTOutsourcingMaterial.setID(wResult);
            } else {
                wResult = wIPTOutsourcingMaterial.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<IPTOutsourcingMaterial> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<Integer>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<String>();
            for (IPTOutsourcingMaterial wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.ipt_outsourcingmaterial WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public IPTOutsourcingMaterial SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        IPTOutsourcingMaterial wResult = new IPTOutsourcingMaterial();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<IPTOutsourcingMaterial> wList = SelectList(wLoginUser, wID, -1, -1, -1, -1, -1, -1, -1, -1, null, null,
                    -1, wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<IPTOutsourcingMaterial> SelectList(BMSEmployee wLoginUser, int wID, int wProductID, int wLineID,
                                                   int wCustomerID, int wOrderID, int wPartID, int wStepID,
                                                   int wMaterialID, int wStatus, Calendar wStartTime,
                                                   Calendar wEndTime, int wTaskStepID, OutResult<Integer> wErrorCode) {
        List<IPTOutsourcingMaterial> wResultList = new ArrayList<IPTOutsourcingMaterial>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT t.*,t1.ProductNo,t2.Name LineName,t3.CustomerName,"
                    + "t4.Name PartName,t5.Name StepName,t6.MaterialNo,t6.MaterialName,t7.Name Creator,t8.OrderNo " +
                    "WBSNo,t9.DemandNo  "
                    + "FROM {0}.ipt_outsourcingmaterial t"
                    + " left join {0}.fpc_product t1 on t.ProductID=t1.ID "
                    + " left join {0}.fmc_line t2 on t.LineID=t2.ID "
                    + " left join {0}.crm_customer t3 on t.CustomerID=t3.ID "
                    + " left join {0}.fpc_part t4 on t.PartID=t4.ID "
                    + " left join {0}.fpc_partpoint t5 on t.StepID=t5.ID "
                    + " left join {0}.mss_material t6 on t.MaterialID=t6.ID "
                    + " left join {0}.mbs_user t7 on t.CreateID=t7.ID "
                    + " left join {0}.oms_order t8 on t.OrderID=t8.ID "
                    + " left join {0}.wms_pickdemand t9 on t.DemandID=t9.ID "
                    + "WHERE  1=1  "
                    + "and ( :wID <= 0 or :wID = t.ID ) " + "and ( :wProductID <= 0 or :wProductID =  t.ProductID ) "
                    + "and ( :wLineID <= 0 or :wLineID =  t.LineID ) "
                    + "and ( :wCustomerID <= 0 or :wCustomerID =  t.CustomerID ) "
                    + "and ( :wOrderID <= 0 or :wOrderID =  t.OrderID ) "
                    + "and ( :wPartID <= 0 or :wPartID =  t.PartID ) "
                    + "and ( :wStepID <= 0 or :wStepID =  t.StepID ) "
                    + "and ( :wTaskStepID <= 0 or :wTaskStepID =  t.TaskStepID ) "
                    + "and ( :wMaterialID <= 0 or :wMaterialID =  t.MaterialID ) "
                    + "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=   t" +
                    ".CreateTime ) "
                    + "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=   t.CreateTime ) "
                    + "and ( :wStatus <= 0 or :wStatus =  t.Status );", wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("wID", wID);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wStepID", wStepID);
            wParamMap.put("wMaterialID", wMaterialID);
            wParamMap.put("wStatus", wStatus);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wTaskStepID", wTaskStepID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                IPTOutsourcingMaterial wItem = new IPTOutsourcingMaterial();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.CustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.StepID = StringUtils.parseInt(wReader.get("StepID"));
                wItem.TaskStepID = StringUtils.parseInt(wReader.get("TaskStepID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.OutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.CreateID = StringUtils.parseInt(wReader.get("CreateID"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.SuplierNo = StringUtils.parseString(wReader.get("SuplierNo"));
                wItem.SuplierName = StringUtils.parseString(wReader.get("SuplierName"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.IPTItemID = StringUtils.parseInt(wReader.get("IPTItemID"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));

                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                wItem.LineName = StringUtils.parseString(wReader.get("LineName"));
                wItem.CustomerName = StringUtils.parseString(wReader.get("CustomerName"));
                wItem.PartName = StringUtils.parseString(wReader.get("PartName"));
                wItem.StepName = StringUtils.parseString(wReader.get("StepName"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.OutSourceTypeText = APSOutSourceType.getEnumType(wItem.OutSourceType).getLable();
                wItem.Creator = StringUtils.parseString(wReader.get("Creator"));
                wItem.StatusText = IPTOutsourcingMaterialStatus.getEnumType(wItem.Status).getLable();

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 获取打印数据
     */
    public MyExcelSheet GetMyExcelSheet(IPTOutsourcingMaterial wIPTOutsourcingMaterial) {
        MyExcelSheet wResult = new MyExcelSheet();
        try {
            wResult.SheetName = "委外拆修入库打印单";
            wResult.TitleName = "委外拆修入库打印单";
            wResult.HeaderList = new ArrayList<>(Arrays.asList("项目号(WBS元素)", "车型", "修程", "配属局段", "车号", "工位", "工序",
                    "物料编码", "物料名称", "数量", "供应商名称", "供应商编码", "序列号", "必修偶修"));
            wResult.DataList = new ArrayList<>();
            List<String> wValueList = new ArrayList<>(Arrays.asList(wIPTOutsourcingMaterial.WBSNo,
                    wIPTOutsourcingMaterial.ProductNo, wIPTOutsourcingMaterial.LineName,
                    wIPTOutsourcingMaterial.CustomerName, wIPTOutsourcingMaterial.PartNo,
                    wIPTOutsourcingMaterial.PartName, wIPTOutsourcingMaterial.StepName,
                    wIPTOutsourcingMaterial.MaterialNo, wIPTOutsourcingMaterial.MaterialName,
                    String.valueOf(wIPTOutsourcingMaterial.FQTY), wIPTOutsourcingMaterial.SuplierName,
                    wIPTOutsourcingMaterial.SuplierNo, wIPTOutsourcingMaterial.SerialNo,
                    wIPTOutsourcingMaterial.OutSourceTypeText));
            wResult.DataList.add(wValueList);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查询自修件的组装工位
     */
    public int IPT_QuerySelfZZPartID(BMSEmployee wLoginUser, int routeID, String materialNo, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t.PartID " +
                            "FROM {0}.fpc_processbom t " +
                            " left join {0}.fpc_routepartpoint t1 on t.RouteID=t1.RouteID and t.PartID=t1.PartID " +
                            " and t.PartPointID=t1.PartPointID " +
                            "where t.routeid=:routeid and t.MATNR=:MATNR and t1.PartPointType=6;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("routeid", routeID);
            wParamMap.put("MATNR", materialNo);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("PartID"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
